/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.jgls.bi.dataprocess;

import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.Date;
import java.util.ArrayList;
import java.util.Iterator;

/**
 *
 * @author zz
 */
public class DataProcessJdbcDao extends JdbcDaoSupport implements DataProcessDao {

    public List getFunction() {
        JdbcTemplate jt = getJdbcTemplate();
        return jt.query("select name from t_bi_function_list where bj=1",
                new RowMapperResultReader(new getFunctionRM()));

    }

    @SuppressWarnings("empty-statement")
    public void TsnAmt(Date adTsnDate, int aiShopId, String asShopBm,
            int aiDateNo, String asYear, int aiYybh, int aiMan_id,String asTname) {
        //开始处理
        int TsnAmt;
        int iRc;
        int aiYear;
        int TsnSort;
        int iWeekAmt=0,iYearAmt=0,iWeekAmt2=0,iYearAmt2=0;
        int isale=0,ispsjk=0,istock=0;
        String sql="";
        float fWeekAddup=0,fYearAddup=0,fWeekAddup2=0,fYearAddup2=0;
        List lstWeek,lstYear;
        Date ldYesterday;
        String ls_mc="";
        System.out.println("now processing Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");
        aiYear = Integer.parseInt(asYear);
        JdbcTemplate jt = getJdbcTemplate();
        Object[] params = null;
        int[] types = null;
        //sale
        if (asShopBm.equalsIgnoreCase("23")){
            sql = "select nvl(count(*),0) from t_waste_book1"  +
                        " where saledate = ? and (bm='23' or bm='23a')  and rownum=1";
            params = new Object[]{adTsnDate};
            types = new int[1];
            types[0] = Types.DATE;
        }else{
            sql = "select nvl(count(*),0) from t_waste_book1"  +
                        " where saledate = ? and bm=? and rownum=1";
            params = new Object[]{adTsnDate, asShopBm};
            types = new int[2];
            types[0] = Types.DATE;
            types[1] = Types.VARCHAR;
        }

        isale = jt.queryForInt(sql, params, types);
        //spsjk
        sql = "select nvl(count(*),0) from kccx"  +
                    " where kcdate = ? and bm=? and rownum=1";
        params = new Object[]{adTsnDate, asShopBm};
        types = new int[2];
        types[0] = Types.DATE;
        types[1] = Types.VARCHAR;
        ispsjk = jt.queryForInt(sql, params, types);
        //sale
        sql = "select nvl(count(*),0) from t_stock"  +
                    " where jhdate = ? and id=? and rownum=1";
        params = new Object[]{adTsnDate, aiShopId};
        types = new int[2];
        types[0] = Types.DATE;
        types[1] = Types.INTEGER;
        istock = jt.queryForInt(sql, params, types);
        //判断
        if (isale>0){
            isale=1;
        }
        if (ispsjk>0){
            ispsjk=1;
        }
        if (istock>0){
            istock=1;
        }

        //begin insert into TsnAmt
        //1:delete
        params = new Object[]{adTsnDate, aiShopId};
        types = new int[]{Types.DATE, Types.INTEGER};
        sql = "delete from wprtl.t_day_upload where sdate = ? and id = ?";
        iRc = jt.update(sql, params, types);
        //2:insert
        sql = "insert into wprtl.t_day_upload " +
                "(sdate,id,sale,spsjk,stock,yybh,bm)" +
                " values(?,?,?,?,?,?,?)";
        params = new Object[]{adTsnDate,aiShopId, isale,ispsjk,istock,aiYybh,asShopBm};
        types = new int[]{Types.DATE, Types.INTEGER, Types.INTEGER,
        Types.INTEGER, Types.INTEGER, Types.INTEGER,Types.VARCHAR};
        iRc = jt.update(sql, params, types);

    }

    public List getShopList(Date adTsnDate,int aiBj) {
        List lTmp;
        List<ArchiDept> lTmp2;
        ArchiDept lad = new ArchiDept();
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "";
        if(aiBj==0){
            sql="select id,bm,mc,pz,man_id,yybh,area from t_archi_dept where ckbj=1  and id not in (794,389) order by id";
            lTmp=jt.query(sql,new RowMapperResultReader(new ArchiDeptRM()));
            
        }else{
            sql="select id,bm,mc,pz,man_id,yybh,area from t_archi_dept where id in (select tsn_id from t_bi_shoplist_noupload where tsn_date = ?)  order by id";
            Object[] params = new Object[]{adTsnDate};
            int[] types = new int[]{Types.DATE};
            lTmp=jt.query(sql,params,types,new RowMapperResultReader(new ArchiDeptRM()));
        }
        lTmp2 = new ArrayList<ArchiDept>();
        Iterator Iter = lTmp.iterator();
        while(Iter.hasNext()){
            lad=(ArchiDept)Iter.next();
            lTmp2.add(lad);
        }
        return lTmp2;
        
    }
    //定义供jdbctemplete使用的内部类
    class getFunctionRM implements RowMapper {

        public Object mapRow(ResultSet rs, int index) throws SQLException {
            return rs.getString(1);
        }
    }

    class ArchiDeptRM implements RowMapper {

        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            ArchiDept ad = new ArchiDept();
            ad.setId(new Integer(rs.getInt("id")));
            ad.setBm(rs.getString("bm"));
            ad.setMc(rs.getString("mc"));
            ad.setPz(new Integer(rs.getInt("pz")));
            ad.setMan_id(new Integer(rs.getInt("man_id")));
            ad.setYybh(new Integer(rs.getInt("yybh")));
            ad.setArea(new Float(rs.getFloat("area")));
            return ad;
        }
    }

    class TsnAmtMxYearRM implements RowMapper {

        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            TsnAmtMx wavg = new TsnAmtMx();
            wavg.setYear_addup(new Float(rs.getFloat("year_addup")));
             return wavg;
        }
    }
    class TsnAmtMxWeekRM implements RowMapper {

        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            TsnAmtMx wavg = new TsnAmtMx();
            wavg.setWeek_addup(new Float(rs.getFloat("week_addup")));
            wavg.setWeek_amt(new Integer(rs.getInt("week_amt")));
            return wavg;
        }
    }
    
}
